package com.ld.hpfs.service.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import com.ld.hpfs.common.utils.Tools;
import com.ld.hpfs.dao.TcodeMapper;
import com.ld.hpfs.service.TcodeService;

@Service
public class TcodeServiceImpl implements TcodeService{

	private static final Logger logger = LoggerFactory.getLogger(TcodeServiceImpl.class);
	
	@Autowired
	private TcodeMapper tcodeMapper;
	
	@Autowired  
    private JdbcTemplate jdbcTemplate;  
	
	@Override
	public ArrayList<HashMap<String,Object>> getEndCodeList(String code){
		return tcodeMapper.getEndCodeList(code);
	}
	
	@Override
	public ArrayList<HashMap<String,Object>> getBeginCodeList(String code){
		return tcodeMapper.getBeginCodeList(code);
	}
	
	@Override
	public ArrayList<HashMap<String,Object>> getSubAccountList(){
		return tcodeMapper.getSubAccountList();
	}
	
	@Override
	public ArrayList<HashMap<String,Object>> getAccountList(HashMap<String,Object> param){
		return tcodeMapper.getAccountList(param);
	}
	
	@Override
	public ArrayList<HashMap<String,Object>> getAccountList1(HashMap<String,Object> param){
		return tcodeMapper.getAccountList1(param);
	}
	
	@Override
	public ArrayList<HashMap<String,Object>> getDetailAccountList(HashMap<String,Object> param){
		return tcodeMapper.getDetailAccountList(param);
	}
	
	@Override
	public HashMap<String,Object> checkGroupID(HashMap<String,Object> param){
		return tcodeMapper.checkGroupID(param);
	}
	
	@Override
	public int insertTcode(HashMap<String,Object> param){
		try{
			tcodeMapper.insertTcode(param);
			return 0;
		}catch(Exception ex){
			logger.error(ex.getMessage());
			return -1;
		}
	}
	
	@Override
	public int updateTcode(HashMap<String,Object> param){
		try{
			tcodeMapper.updateTcode(param);
			return 0;
		}catch(Exception ex){
			logger.error(ex.getMessage());
			return -1;
		}
	}
	
	@Override
	public int deleteTcode(HashMap<String,Object> param){
		try{
			tcodeMapper.deleteTcode(param);
			return 0;
		}catch(Exception ex){
			logger.error(ex.getMessage());
			return -1;
		}
	}
	
	@Override
	public ArrayList<HashMap<String,Object>> getTcodeList(HashMap<String,Object> param)
	{
		int count = tcodeMapper.getTcodeListCount(param);
		if(count == 0){
			return null;
		}else{
			param.put("pageCount", count);
			return tcodeMapper.getTcodeList(param);
		}
	}	
	
	@Override
	public ArrayList<HashMap<String,Object>> getTcodeListForImport(int id){
		return tcodeMapper.getTcodeListForImport(id);
	}
	
    @Override
    public int insertTcodeForImport(HashMap<String,Object> param){
    	try{
    		tcodeMapper.insertTcodeForImport(param);
    		tcodeMapper.updateTcodeForImport(param);
			return 0;
		}catch(Exception ex){
			logger.error(ex.getMessage());
			return -1;
		}
    }
    
    @Override
	public ArrayList<HashMap<String,Object>> getSearchAccountList(HashMap<String,Object> param){
//		StringBuffer sql =new StringBuffer("select distinct LOCAL_ACCOUNT as text, LOCAL_ACCOUNT as value from HPFS_D_GENERAL_LEDGER " +
//				"where LOCAL_ACCOUNT is not null and LOCAL_ACCOUNT != '' and COMPANY_ID = "+param.get("company_id").toString()+" order by LOCAL_ACCOUNT");
 		StringBuffer sql =new StringBuffer("SELECT a.local_account_desc as text, a.local_account as value from HPFS_C_ACCOUNT a, HPFS_C_COMPANY b"+
 		        " where a.group_id = b.group_id and b.company_hid = "+param.get("company_id").toString()+ " order by a.local_account");
		@SuppressWarnings("unchecked")
		ArrayList<HashMap<String,Object>> result = (ArrayList<HashMap<String, Object>>) jdbcTemplate.query(sql.toString(),new RowMapper() {     
          public Object mapRow(ResultSet rs, int rowNum) throws SQLException {     
        	  HashMap<String,Object> post = new HashMap<String,Object>();
        	  post.put("text", rs.getObject(1));
        	  post.put("value", rs.getObject(2));
              return post;     
          }    
	    });	
		return result;
		//return tcodeMapper.getSearchAccountList(param);
	}
    
    @Override
	public ArrayList<HashMap<String,Object>> getSearchNoList(HashMap<String,Object> param){
    	StringBuffer sql =new StringBuffer("select distinct ENTRY_NO as text, ENTRY_NO as value from HPFS_D_GENERAL_LEDGER " +
				"where ENTRY_NO is not null and ENTRY_NO != '' and COMPANY_ID = "+param.get("company_id").toString()+" order by ENTRY_NO");
    	
		@SuppressWarnings("unchecked")
		ArrayList<HashMap<String,Object>> result = (ArrayList<HashMap<String, Object>>) jdbcTemplate.query(sql.toString(),new RowMapper() {     
          public Object mapRow(ResultSet rs, int rowNum) throws SQLException {     
        	  HashMap<String,Object> post = new HashMap<String,Object>();
        	  post.put("text", rs.getObject(1));
        	  post.put("value", rs.getObject(2));
              return post;     
          }    
	    });	
		return result;
		//return tcodeMapper.getSearchNoList(param);
	}
    
    @Override
	public ArrayList<HashMap<String,Object>> getSearchAmountList(){
		return tcodeMapper.getSearchAmountList();
	}
    
    @Override
	public ArrayList<HashMap<String,Object>> getMakeUserList(){
		return tcodeMapper.getMakeUserList();
	}
    
    @SuppressWarnings("unchecked")
	@Override
	public ArrayList<HashMap<String,Object>> getSearchList(HashMap<String,Object> param)
	{
    	StringBuffer temp = new StringBuffer("");
    	StringBuffer conSql = new StringBuffer("select count(*) as num from (select * from HPFS_D_GENERAL_LEDGER where 1 = 1");
    	StringBuffer condition = new StringBuffer("");
		StringBuffer sql =new StringBuffer("	select * from ( select top 100 percent *,row_number() over ( order by "+param.get("orderBy")+") as rn " +
				"from ( select GENERAL_LEDGER_HID,FISCAL_YEAR,FISCAL_PERIOD,isnull(LOCK_DESC,0) as LOCK_DESC " +
				",isnull(LOCAL_ACCOUNT,'') as LOCAL_ACCOUNT,ENTRY_NO,ENTRY_NO_NEW,AMOUNT_LOCALDB,AMOUNT_LOCALCR " +
				",isnull(CONVERT(varchar(100),POSTING_DATE, 23),'') as POSTING_DATE" +
				",isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') as ENTRY_DATE ,LCURRENCY,isnull(SENTRY_DESC1,'') as SENTRY_DESC1" +
				",isnull(LOCAL_ACCOUNT_DESC,'') as LOCAL_ACCOUNT_DESC, isnull(CHINA_DESC,'') as CHINA_DESC" +
				",isnull(ERP_ALL_ACCOUNT,'') as ERP_ALL_ACCOUNT, isnull(ERP_ALL_ACCOUNT_DESC,'') as ERP_ALL_ACCOUNT_DESC" +
				",isnull(REFRENCE1,'') as REFRENCE1" +
				", isnull(REFRENCE2,'') as REFRENCE2, isnull(REFRENCE3,'') as REFRENCE3" +
				", isnull(REFRENCE4,'') as REFRENCE4, isnull(REFRENCE5,'') as REFRENCE5" +
				",isnull(REFRENCE6,'') as REFRENCE6" +
				", isnull(REFRENCE7,'') as REFRENCE7, isnull(REFRENCE8,'') as REFRENCE8, isnull(REFRENCE9,'') as REFRENCE9" +
				" , isnull(REFRENCE10,'') as REFRENCE10, isnull(REFRENCE11,'') as REFRENCE11, isnull(REFRENCE12,'') as REFRENCE12" +
				", isnull(REFRENCE13,'') as REFRENCE13, isnull(REFRENCE14,'') as REFRENCE14" +
				", isnull(REFRENCE15,'') as REFRENCE15, isnull(REFRENCE16,'') as REFRENCE16,isnull(REFRENCE17,'') as REFRENCE17" +
				", isnull(REFRENCE18,'') as REFRENCE18, isnull(REFRENCE19,'') as REFRENCE19" +
				", isnull(REFRENCE20,'') as REFRENCE20,isnull(OPERATER,'') as OPERATER,POST_STATUS,IMPORT_TYPE from HPFS_D_GENERAL_LEDGER as s where 1 = 1");
		if(!Tools.isEmpty(param.get("company_id"))){
			condition.append(" and COMPANY_ID = " + param.get("company_id").toString());
		}
		if(!Tools.isEmpty(param.get("beginAccount")) 
				&& !Tools.isEmpty(param.get("endAccount")) && !Tools.isEmpty(param.get("accountCode"))) {
			condition.append(" and ((LOCAL_ACCOUNT >= '"+param.get("beginAccount").toString()+"' and LOCAL_ACCOUNT <= '"+param.get("endAccount").toString()+"') or LOCAL_ACCOUNT in (");
			for(HashMap<String,String> obj : (List<HashMap<String,String>>)param.get("list")){
				temp.append("'"+obj.get("id")+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+"))");
		}
		if(!Tools.isEmpty(param.get("beginAccount")) 
				&& !Tools.isEmpty(param.get("endAccount")) && Tools.isEmpty(param.get("accountCode"))) {
			condition.append(" and LOCAL_ACCOUNT >= '"+param.get("beginAccount").toString()+"' and LOCAL_ACCOUNT <= '"+param.get("endAccount").toString()+"'");
		}
		if(!Tools.isEmpty(param.get("beginAccount")) 
				&& Tools.isEmpty(param.get("endAccount")) && !Tools.isEmpty(param.get("accountCode"))) {
			condition.append(" and (LOCAL_ACCOUNT >= '"+param.get("beginAccount").toString()+"' or LOCAL_ACCOUNT in (");
			for(HashMap<String,String> obj : (List<HashMap<String,String>>)param.get("list")){
				temp.append("'"+obj.get("id")+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+"))");
		}
		temp = new StringBuffer("");
		if(Tools.isEmpty(param.get("beginAccount")) 
				&& !Tools.isEmpty(param.get("endAccount")) && !Tools.isEmpty(param.get("accountCode"))) {
			condition.append(" and (LOCAL_ACCOUNT <= '"+param.get("endAccount").toString()+"' or LOCAL_ACCOUNT in (");
			for(HashMap<String,String> obj : (List<HashMap<String,String>>)param.get("list")){
				temp.append("'"+obj.get("id")+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+"))");
		}
		temp = new StringBuffer("");
		if(Tools.isEmpty(param.get("beginAccount")) 
				&& !Tools.isEmpty(param.get("endAccount")) && Tools.isEmpty(param.get("accountCode"))) {
			condition.append(" and LOCAL_ACCOUNT <= '"+param.get("endAccount").toString()+"'");
		}
		if(!Tools.isEmpty(param.get("beginAccount")) 
				&& Tools.isEmpty(param.get("endAccount")) && Tools.isEmpty(param.get("accountCode"))) {
			condition.append(" and LOCAL_ACCOUNT >= '"+param.get("beginAccount").toString()+"'");
		}
		temp = new StringBuffer("");
		if(Tools.isEmpty(param.get("beginAccount")) 
				&& Tools.isEmpty(param.get("endAccount")) && !Tools.isEmpty(param.get("accountCode"))) {
			condition.append(" and LOCAL_ACCOUNT in (");
			for(HashMap<String,String> obj : (List<HashMap<String,String>>)param.get("list")){
				temp.append("'"+obj.get("id")+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+")");
		}
		if(!Tools.isEmpty(param.get("account_s_day"))){
			condition.append(" and isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') >= CONVERT(varchar(100),'" + param.get("account_s_day").toString()+"')");
		}
		if(!Tools.isEmpty(param.get("account_e_day"))){
			condition.append(" and isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') <= CONVERT(varchar(100),'" + param.get("account_e_day").toString()+"')");
		}
		temp = new StringBuffer("");
		if(!Tools.isEmpty(param.get("beginNo")) 
				&& !Tools.isEmpty(param.get("endNo")) && !Tools.isEmpty(param.get("noCode"))) {
			condition.append(" and ((ENTRY_NO >= '"+param.get("beginNo").toString()+"' and ENTRY_NO <= '"+param.get("endNo").toString()+"') or ENTRY_NO in (");
			for(String obj : (List<String>)param.get("ids")){
				temp.append("'"+obj+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+"))");
		}
		if(!Tools.isEmpty(param.get("beginNo")) 
				&& !Tools.isEmpty(param.get("endNo")) && Tools.isEmpty(param.get("noCode"))) {
			condition.append(" and ENTRY_NO >= '"+param.get("beginNo").toString()+"' and ENTRY_NO <= '"+param.get("endNo").toString()+"'");
		}
		if(!Tools.isEmpty(param.get("beginNo")) 
				&& Tools.isEmpty(param.get("endNo")) && !Tools.isEmpty(param.get("noCode"))) {
			condition.append(" and (ENTRY_NO >= '"+param.get("beginNo").toString()+"' or ENTRY_NO in (");
			for(String obj : (List<String>)param.get("ids")){
				temp.append("'"+obj+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+"))");
		}
		temp = new StringBuffer("");
		if(Tools.isEmpty(param.get("beginNo")) 
				&& !Tools.isEmpty(param.get("endNo")) && !Tools.isEmpty(param.get("noCode"))) {
			condition.append(" and (ENTRY_NO <= '"+param.get("endNo").toString()+"' or ENTRY_NO in (");
			for(String obj : (List<String>)param.get("ids")){
				temp.append("'"+obj+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+"))");
		}
		temp = new StringBuffer("");
		if(Tools.isEmpty(param.get("beginNo")) 
				&& !Tools.isEmpty(param.get("endNo")) && Tools.isEmpty(param.get("noCode"))) {
			condition.append(" and ENTRY_NO <= '"+param.get("endNo").toString()+"'");
		}
		if(!Tools.isEmpty(param.get("beginNo")) 
				&& Tools.isEmpty(param.get("endNo")) && Tools.isEmpty(param.get("noCode"))) {
			condition.append(" and ENTRY_NO >= '"+param.get("beginNo").toString()+"'");
		}
		temp = new StringBuffer("");
		if(Tools.isEmpty(param.get("beginNo")) 
				&& Tools.isEmpty(param.get("endNo")) && !Tools.isEmpty(param.get("noCode"))) {
			condition.append(" and ENTRY_NO in (");
			for(String obj : (List<String>)param.get("ids")){
				temp.append("'"+obj+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+")");
		}
		if(!Tools.isEmpty(param.get("no_s_day"))){
			condition.append(" and isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') >= CONVERT(varchar(100),'" + param.get("no_s_day").toString()+"')");
		}
		if(!Tools.isEmpty(param.get("no_e_day"))){
			condition.append(" and isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') <= CONVERT(varchar(100),'" + param.get("no_e_day").toString()+"')");
		}
		if(!Tools.isEmpty(param.get("beginAmount"))){
			condition.append(" and AMOUNT_LOCALDB >= " + param.get("beginAmount").toString());
		}
		if(!Tools.isEmpty(param.get("endAmount"))){
			condition.append(" and AMOUNT_LOCALDB <= " + param.get("endAmount").toString());
		}
		if(!Tools.isEmpty(param.get("amount_s_day"))){
			condition.append(" and isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') >= CONVERT(varchar(100),'" + param.get("amount_s_day").toString()+"')");
		}
		if(!Tools.isEmpty(param.get("amount_e_day"))){
			condition.append(" and isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') <= CONVERT(varchar(100),'" + param.get("amount_e_day").toString()+"')");
		}
		if(!Tools.isEmpty(param.get("note_s_day"))){
			condition.append(" and isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') >= CONVERT(varchar(100),'" + param.get("note_s_day").toString()+"')");
		}
		if(!Tools.isEmpty(param.get("note_e_day"))){
			condition.append(" and isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') <= CONVERT(varchar(100),'" + param.get("note_e_day").toString()+"')");
		}
		if(!Tools.isEmpty(param.get("noteTxt"))){
			condition.append(" and SENTRY_DESC1 like '" + param.get("noteTxt").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("noteTxt"))){
			condition.append(" and SENTRY_DESC1 like '" + param.get("noteTxt").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence1"))){
			condition.append(" and REFRENCE1 like '" + param.get("refrence1").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence2"))){
			condition.append(" and REFRENCE2 like '" + param.get("refrence2").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence3"))){
			condition.append(" and REFRENCE3 like '" + param.get("refrence3").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence4"))){
			condition.append(" and REFRENCE4 like '" + param.get("refrence4").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence5"))){
			condition.append(" and REFRENCE5 like '" + param.get("refrence5").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence6"))){
			condition.append(" and REFRENCE6 like '" + param.get("refrence6").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence7"))){
			condition.append(" and REFRENCE7 like '" + param.get("refrence7").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence8"))){
			condition.append(" and REFRENCE8 like '" + param.get("refrence8").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence9"))){
			condition.append(" and REFRENCE9 like '" + param.get("refrence9").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence10"))){
			condition.append(" and REFRENCE10 like '" + param.get("refrence10").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence11"))){
			condition.append(" and REFRENCE11 like '" + param.get("refrence11").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence12"))){
			condition.append(" and REFRENCE12 like '" + param.get("refrence12").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence13"))){
			condition.append(" and REFRENCE13 like '" + param.get("refrence13").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence14"))){
			condition.append(" and REFRENCE14 like '" + param.get("refrence14").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence15"))){
			condition.append(" and REFRENCE15 like '" + param.get("refrence15").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence16"))){
			condition.append(" and REFRENCE16 like '" + param.get("refrence16").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence17"))){
			condition.append(" and REFRENCE17 like '" + param.get("refrence17").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence18"))){
			condition.append(" and REFRENCE18 like '" + param.get("refrence18").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence19"))){
			condition.append(" and REFRENCE19 like '" + param.get("refrence19").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence20"))){
			condition.append(" and REFRENCE20 like '" + param.get("refrence20").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("fiscal_year"))){
			condition.append(" and FISCAL_YEAR = '" + param.get("fiscal_year").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("fiscal_period"))){
			condition.append(" and FISCAL_PERIOD = '" + param.get("fiscal_period").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("makeUser"))){
			condition.append(" and OPERATER like '" + param.get("makeUser").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("is_includ"))){
			if(param.get("is_includ").equals("2"))
				condition.append(" and POST_STATUS = 1");
			if(param.get("is_includ").equals("3"))
				condition.append(" and POST_STATUS = 0");
		}
		temp = new StringBuffer("");
		if(!Tools.isEmpty(param.get("items"))) {
			condition.append(" and LCURRENCY in (");
			for(String obj : (List<String>)param.get("ids1")){
				temp.append("'"+obj+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+")");
		}

		sql.append(condition+") as a) s where rn <= "+param.get("pageEnd")+" and rn >= "+param.get("pageStart"));
		
		conSql.append(condition+") itemsCount");
		
		long count = 1;
		
		if(param.get("mark").equals("0")){
			count = jdbcTemplate.queryForLong(conSql.toString());
		}

	//	int count = tcodeMapper.getSearchListCount(param);
		if(count == 0){
			return null;
		}else{
			if(param.get("mark").equals("0")){
				param.put("pageCount", count);
			} else {
				param.put("pageCount", param.get("count"));
			}
			
			ArrayList<HashMap<String,Object>> result = (ArrayList<HashMap<String, Object>>) jdbcTemplate.query(sql.toString(),new RowMapper() {     
		          public Object mapRow(ResultSet rs, int rowNum) throws SQLException {     
		        	  HashMap<String,Object> post = new HashMap<String,Object>();
		        	  post.put("GENERAL_LEDGER_HID", rs.getObject(1));
		        	  post.put("FISCAL_YEAR", rs.getObject(2));
		        	  post.put("FISCAL_PERIOD", rs.getObject(3));
		        	  post.put("LOCK_DESC", rs.getObject(4));
		        	  post.put("LOCAL_ACCOUNT", rs.getObject(5));
		        	  post.put("ENTRY_NO", rs.getObject(6));
		        	  post.put("ENTRY_NO_NEW", rs.getObject(7));
		        	  post.put("AMOUNT_LOCALDB", rs.getObject(8));
		        	  post.put("AMOUNT_LOCALCR", rs.getObject(9));
		        	  post.put("POSTING_DATE", rs.getObject(10));
		        	  post.put("ENTRY_DATE", rs.getObject(11));
		        	  post.put("LCURRENCY", rs.getObject(12));
		        	  post.put("SENTRY_DESC1", rs.getObject(13));
		        	  post.put("LOCAL_ACCOUNT_DESC", rs.getObject(14));
		        	  post.put("CHINA_DESC", rs.getObject(15));
		        	  post.put("ERP_ALL_ACCOUNT", rs.getObject(16));
		        	  post.put("ERP_ALL_ACCOUNT_DESC", rs.getObject(17));
		        	  post.put("REFRENCE1", rs.getObject(18));
		        	  post.put("REFRENCE2", rs.getObject(19));
		        	  post.put("REFRENCE3", rs.getObject(20));
		        	  post.put("REFRENCE4", rs.getObject(21));
		        	  post.put("REFRENCE5", rs.getObject(22));
		        	  post.put("REFRENCE6", rs.getObject(23));
		        	  post.put("REFRENCE7", rs.getObject(24));
		        	  post.put("REFRENCE8", rs.getObject(25));
		        	  post.put("REFRENCE9", rs.getObject(26));
		        	  post.put("REFRENCE10", rs.getObject(27));
		        	  post.put("REFRENCE11", rs.getObject(28));
		        	  post.put("REFRENCE12", rs.getObject(29));
		        	  post.put("REFRENCE13", rs.getObject(30));
		        	  post.put("REFRENCE14", rs.getObject(31));
		        	  post.put("REFRENCE15", rs.getObject(32));
		        	  post.put("REFRENCE16", rs.getObject(33));
		        	  post.put("REFRENCE17", rs.getObject(34));
		        	  post.put("REFRENCE18", rs.getObject(35));
		        	  post.put("REFRENCE19", rs.getObject(36));
		        	  post.put("REFRENCE20", rs.getObject(37));
		        	  post.put("OPERATER", rs.getObject(38));
		        	  post.put("POST_STATUS", rs.getObject(39));
		        	  post.put("IMPORT_TYPE", rs.getObject(40));
		              return post;     
		          }    
		       });		
			return result;
		}
	}	
    
    @Override
    public ArrayList<HashMap<String,Object>> getAccountDetailList(HashMap<String,Object> param){
    	StringBuffer conSql = new StringBuffer("select count(*) as num from (select * from HPFS_D_GENERAL_LEDGER where 1 = 1");
    	StringBuffer condition = new StringBuffer("");
		StringBuffer sql =new StringBuffer("	select * from ( select top 100 percent *,row_number() over ( order by "+param.get("orderBy")+") as rn " +
				"from ( " +
				"select null fiscal_period, null fiscal_year, " + param.get("company_id").toString() +" company_id, null date_time, " +
				" max(currency) lcu, '"+ param.get("account").toString() +"' local_account," +
				" (select max(local_account_desc) from hpfs_c_account where local_account = "+ param.get("account").toString() +" and group_id in " +
						"(select group_id from hpfs_c_company where company_hid="+param.get("company_id").toString()+")) local_account_desc, " +
				"null china_desc, null erp_all_account, null erp_all_account_desc," +
				"null refrence1, null refrence2, null refrence3, null refrence4, null refrence5, " +
				"null refrence6, null refrence7, null refrence8, null refrence9, null refrence10," +
				"null refrence11, null refrence12, null refrence13, null refrence14, null refrence15," +
				"null refrence16, null refrence17, null refrence18, null refrence19, null refrence20, " +
				"'' entry_no, null sentry_desc1, isnull(sum(a.local_amount_db),0) db, isnull(sum(a.local_amount_cr),0) cr," +
				"isnull(sum(a.local_amount_db),0) - isnull(sum(a.local_amount_cr),0) amount1," +
				"null otherAmount, '' otherAccount, '' ENTRY_NO_NEW" +
				" from hpfs_a_balance_ex a where a.company_id=" + param.get("company_id").toString() + " and a.local_account='" + param.get("account").toString() + "'" +
				" and cast(a.fiscal_year as integer)*100 + cast(a.fiscal_period as integer) < " + param.get("fiscal_year").toString() +"*100 + " + param.get("fiscal_period").toString() + 
				" union all " +
				" select fiscal_period,fiscal_year,company_id,isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') as date_time" +
				",LCURRENCY as lcu, LOCAL_ACCOUNT as local_account, LOCAL_ACCOUNT_DESC as account_desc" +
  			",CHINA_DESC, ERP_ALL_ACCOUNT, ERP_ALL_ACCOUNT_DESC, REFRENCE1, REFRENCE2, REFRENCE3, REFRENCE4, REFRENCE5" +
 			",REFRENCE6, REFRENCE7, REFRENCE8, REFRENCE9, REFRENCE10, REFRENCE11, REFRENCE12, REFRENCE13, REFRENCE14, REFRENCE15, REFRENCE16" +
 			",REFRENCE17, REFRENCE18, REFRENCE19, REFRENCE20,ENTRY_NO,SENTRY_DESC1" +
			",case when '"+param.get("type").toString()+"' in ('1', '3') then AMOUNT_LOCALDB else null end as db" +
			",case when '"+param.get("type").toString()+"' in ('2', '3') then AMOUNT_LOCALCR else null end as cr" +
			",case when '"+param.get("type").toString()+"' = '3' then isnull(AMOUNT_LOCALDB,0) - isnull(AMOUNT_LOCALCR,0)" +
			" else null end as amount1" +
			",case when AMOUNT_LOCALDB != 0 " +
			"  	then (select max(AMOUNT_LOCALCR) from HPFS_D_GENERAL_LEDGER where ENTRY_NO = s.ENTRY_NO and FISCAL_YEAR = s.FISCAL_YEAR and FISCAL_PERIOD = s.FISCAL_PERIOD and COMPANY_ID = s.COMPANY_ID and LOCAL_ACCOUNT != s.LOCAL_ACCOUNT)" +
			"  	when AMOUNT_LOCALCR != 0" +
		  	"	then (select max(AMOUNT_LOCALDB) from HPFS_D_GENERAL_LEDGER where ENTRY_NO = s.ENTRY_NO and FISCAL_YEAR = s.FISCAL_YEAR and FISCAL_PERIOD = s.FISCAL_PERIOD and COMPANY_ID = s.COMPANY_ID and LOCAL_ACCOUNT != s.LOCAL_ACCOUNT)" +
			" end as otherAmount" +
			",case when AMOUNT_LOCALDB != 0 then " +
			" 	isnull((select top 1 LOCAL_ACCOUNT from HPFS_D_GENERAL_LEDGER where ENTRY_NO = s.ENTRY_NO and FISCAL_YEAR = s.FISCAL_YEAR and FISCAL_PERIOD = s.FISCAL_PERIOD and COMPANY_ID = s.COMPANY_ID and LOCAL_ACCOUNT != s.LOCAL_ACCOUNT  " +
			"	and AMOUNT_LOCALCR = (select max(AMOUNT_LOCALCR) from HPFS_D_GENERAL_LEDGER where ENTRY_NO = s.ENTRY_NO and FISCAL_YEAR = s.FISCAL_YEAR and FISCAL_PERIOD = s.FISCAL_PERIOD and COMPANY_ID = s.COMPANY_ID and LOCAL_ACCOUNT != s.LOCAL_ACCOUNT)),'')" +
			" 	when AMOUNT_LOCALCR != 0 then" +
		 	"	isnull((select top 1 LOCAL_ACCOUNT from HPFS_D_GENERAL_LEDGER where ENTRY_NO = s.ENTRY_NO and FISCAL_YEAR = s.FISCAL_YEAR and FISCAL_PERIOD = s.FISCAL_PERIOD and COMPANY_ID = s.COMPANY_ID and LOCAL_ACCOUNT != s.LOCAL_ACCOUNT " +
			" 	and AMOUNT_LOCALDB = (select max(AMOUNT_LOCALDB) from HPFS_D_GENERAL_LEDGER where ENTRY_NO = s.ENTRY_NO and FISCAL_YEAR = s.FISCAL_YEAR and FISCAL_PERIOD = s.FISCAL_PERIOD and COMPANY_ID = s.COMPANY_ID and LOCAL_ACCOUNT != s.LOCAL_ACCOUNT)),'')" +
			" end as otherAccount,ENTRY_NO_NEW" +
			" from HPFS_D_GENERAL_LEDGER as s where 1=1");

		if(param.get("type").toString().equals("1"))
		{
			condition.append(" and AMOUNT_LOCALDB <> 0");
		}

		if(param.get("type").toString().equals("2"))
		{
			condition.append(" and AMOUNT_LOCALCR <> 0");
		}

		if(!Tools.isEmpty(param.get("account"))){
			condition.append(" and local_account = '" + param.get("account").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("fiscal_year"))){
			condition.append(" and fiscal_year = '" + param.get("fiscal_year").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("fiscal_period"))){
			condition.append(" and fiscal_period = '" + param.get("fiscal_period").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("company_id"))){
			condition.append(" and company_id = '" + param.get("company_id").toString() +"'");
		}
		sql.append(condition+") as a) s where rn <= "+param.get("pageEnd")+" and rn >= "+param.get("pageStart"));
		
		conSql.append(condition+") itemsCount");
    	long count = jdbcTemplate.queryForLong(conSql.toString());
    	param.put("pageCount", count);
		if(count == 0){
			return null;
		}else{
			@SuppressWarnings("unchecked")
			ArrayList<HashMap<String,Object>> result = (ArrayList<HashMap<String, Object>>) jdbcTemplate.query(sql.toString(),new RowMapper() {     
		          public Object mapRow(ResultSet rs, int rowNum) throws SQLException {     
		        	  HashMap<String,Object> post = new HashMap<String,Object>();
		        	  post.put("fiscal_period", rs.getObject(1));
		        	  post.put("fiscal_year", rs.getObject(2));
		        	  post.put("company_id", rs.getObject(3));
		        	  post.put("date_time", rs.getObject(4));
		        	  post.put("lcu", rs.getObject(5));
		        	  post.put("local_account", rs.getObject(6));
		        	  post.put("account_desc", rs.getObject(7));
		        	  post.put("CHINA_DESC", rs.getObject(8));
		        	  post.put("ERP_ALL_ACCOUNT", rs.getObject(9));
		        	  post.put("ERP_ALL_ACCOUNT_DESC", rs.getObject(10));
		        	  post.put("REFRENCE1", rs.getObject(11));
		        	  post.put("REFRENCE2", rs.getObject(12));
		        	  post.put("REFRENCE3", rs.getObject(13));
		        	  post.put("REFRENCE4", rs.getObject(14));
		        	  post.put("REFRENCE5", rs.getObject(15));
		        	  post.put("REFRENCE6", rs.getObject(16));
		        	  post.put("REFRENCE7", rs.getObject(17));
		        	  post.put("REFRENCE8", rs.getObject(18));
		        	  post.put("REFRENCE9", rs.getObject(19));
		        	  post.put("REFRENCE10", rs.getObject(20));
		        	  post.put("REFRENCE11", rs.getObject(21));
		        	  post.put("REFRENCE12", rs.getObject(22));
		        	  post.put("REFRENCE13", rs.getObject(23));
		        	  post.put("REFRENCE14", rs.getObject(24));
		        	  post.put("REFRENCE15", rs.getObject(25));
		        	  post.put("REFRENCE16", rs.getObject(26));
		        	  post.put("REFRENCE17", rs.getObject(27));
		        	  post.put("REFRENCE18", rs.getObject(28));
		        	  post.put("REFRENCE19", rs.getObject(29));
		        	  post.put("REFRENCE20", rs.getObject(30));
		        	  post.put("ENTRY_NO", rs.getObject(31));
		        	  post.put("SENTRY_DESC1", rs.getObject(32));
		        	  post.put("db", rs.getObject(33));
		        	  post.put("cr", rs.getObject(34));
		        	  post.put("amount1", rs.getObject(35));
		        	  post.put("otherAmount", rs.getObject(36));
		        	  post.put("otherAccount", rs.getObject(37));
		        	  post.put("ENTRY_NO_NEW", rs.getObject(38));
		              return post;     
		          }    
		       });	
			double sum_amount = 0;
			if(param.get("type").toString().equals("3")){
				for(int i = 0;i<result.size();i++){
					HashMap<String,Object> obj = result.get(i);
					if(i == 0){
						sum_amount = Double.parseDouble(obj.get("amount1").toString());
					} else {
						sum_amount += Double.parseDouble(obj.get("amount1").toString());
					}

					DecimalFormat df =  new DecimalFormat("#.##");
					String priceStr=df.format(sum_amount);
					obj.put("amount", priceStr);
				//	result.add(obj);
		    	}
			}
			return result;
		}
   // 	param.put("pageCount", count);
	//	return tcodeMapper.getAccountDetailList(param);
//		if(count == 0){
//			return null;
//		}else{
//			param.put("pageCount", count);
//			return tcodeMapper.getAccountDetailList(param);
//		}
    }
    
    @Override
    public int getAccountDetailCount(HashMap<String,Object> param){

    	StringBuffer conSql = new StringBuffer("select count(*) as num from (select * from HPFS_D_GENERAL_LEDGER where 1 = 1");
    	StringBuffer condition = new StringBuffer("");
		
    	
		if(!Tools.isEmpty(param.get("account"))){
			condition.append(" and local_account = '" + param.get("account").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("fiscal_year"))){
			condition.append(" and fiscal_year = '" + param.get("fiscal_year").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("fiscal_period"))){
			condition.append(" and fiscal_period = '" + param.get("fiscal_period").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("company_id"))){
			condition.append(" and company_id = '" + param.get("company_id").toString() +"'");
		}
		
		conSql.append(condition+") itemsCount");
    	long count = jdbcTemplate.queryForLong(conSql.toString());
    	return (int)count;
		
    	
    	//return tcodeMapper.getAccountDetailCount(param);
    }
    
    @Override
    public ArrayList<HashMap<String,Object>> getSearchListForExport(HashMap<String,Object> param){

    	StringBuffer temp = new StringBuffer("");
    	StringBuffer conSql = new StringBuffer("select count(*) as num from (select * from HPFS_D_GENERAL_LEDGER where 1 = 1");
    	StringBuffer condition = new StringBuffer("");
    	StringBuffer sql =new StringBuffer("	select * from ( select top 100 percent *,row_number() over ( order by "+param.get("orderBy")+") as rn " +
				"from ( select GENERAL_LEDGER_HID,FISCAL_YEAR,FISCAL_PERIOD,isnull(LOCK_DESC,0) as LOCK_DESC " +
				",isnull(LOCAL_ACCOUNT,'') as LOCAL_ACCOUNT,ENTRY_NO,ENTRY_NO_NEW,AMOUNT_LOCALDB,AMOUNT_LOCALCR " +
				",isnull(CONVERT(varchar(100),POSTING_DATE, 23),'') as POSTING_DATE" +
				",isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') as ENTRY_DATE ,LCURRENCY,isnull(SENTRY_DESC1,'') as SENTRY_DESC1" +
				",isnull(LOCAL_ACCOUNT_DESC,'') as LOCAL_ACCOUNT_DESC, isnull(CHINA_DESC,'') as CHINA_DESC" +
				",isnull(ERP_ALL_ACCOUNT,'') as ERP_ALL_ACCOUNT, isnull(ERP_ALL_ACCOUNT_DESC,'') as ERP_ALL_ACCOUNT_DESC" +
				",isnull(REFRENCE1,'') as REFRENCE1" +
				", isnull(REFRENCE2,'') as REFRENCE2, isnull(REFRENCE3,'') as REFRENCE3" +
				", isnull(REFRENCE4,'') as REFRENCE4, isnull(REFRENCE5,'') as REFRENCE5" +
				",isnull(REFRENCE6,'') as REFRENCE6" +
				", isnull(REFRENCE7,'') as REFRENCE7, isnull(REFRENCE8,'') as REFRENCE8, isnull(REFRENCE9,'') as REFRENCE9" +
				" , isnull(REFRENCE10,'') as REFRENCE10, isnull(REFRENCE11,'') as REFRENCE11, isnull(REFRENCE12,'') as REFRENCE12" +
				", isnull(REFRENCE13,'') as REFRENCE13, isnull(REFRENCE14,'') as REFRENCE14" +
				", isnull(REFRENCE15,'') as REFRENCE15, isnull(REFRENCE16,'') as REFRENCE16,isnull(REFRENCE17,'') as REFRENCE17" +
				", isnull(REFRENCE18,'') as REFRENCE18, isnull(REFRENCE19,'') as REFRENCE19" +
				", isnull(REFRENCE20,'') as REFRENCE20,isnull(OPERATER,'') as OPERATER,POST_STATUS,IMPORT_TYPE from HPFS_D_GENERAL_LEDGER as s where 1 = 1");
		if(!Tools.isEmpty(param.get("company_id"))){
			condition.append(" and COMPANY_ID = " + param.get("company_id").toString());
		}
		if(!Tools.isEmpty(param.get("beginAccount")) 
				&& !Tools.isEmpty(param.get("endAccount")) && !Tools.isEmpty(param.get("accountCode"))) {
			condition.append(" and ((LOCAL_ACCOUNT >= '"+param.get("beginAccount").toString()+"' and LOCAL_ACCOUNT <= '"+param.get("endAccount").toString()+"') or LOCAL_ACCOUNT in (");
			for(HashMap<String,String> obj : (List<HashMap<String,String>>)param.get("list")){
				temp.append("'"+obj.get("id")+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+"))");
		}
		if(!Tools.isEmpty(param.get("beginAccount")) 
				&& !Tools.isEmpty(param.get("endAccount")) && Tools.isEmpty(param.get("accountCode"))) {
			condition.append(" and LOCAL_ACCOUNT >= '"+param.get("beginAccount").toString()+"' and LOCAL_ACCOUNT <= '"+param.get("endAccount").toString()+"'");
		}
		if(!Tools.isEmpty(param.get("beginAccount")) 
				&& Tools.isEmpty(param.get("endAccount")) && !Tools.isEmpty(param.get("accountCode"))) {
			condition.append(" and (LOCAL_ACCOUNT >= '"+param.get("beginAccount").toString()+"' or LOCAL_ACCOUNT in (");
			for(HashMap<String,String> obj : (List<HashMap<String,String>>)param.get("list")){
				temp.append("'"+obj.get("id")+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+"))");
		}
		temp = new StringBuffer("");
		if(Tools.isEmpty(param.get("beginAccount")) 
				&& !Tools.isEmpty(param.get("endAccount")) && !Tools.isEmpty(param.get("accountCode"))) {
			condition.append(" and (LOCAL_ACCOUNT <= '"+param.get("endAccount").toString()+"' or LOCAL_ACCOUNT in (");
			for(HashMap<String,String> obj : (List<HashMap<String,String>>)param.get("list")){
				temp.append("'"+obj.get("id")+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+"))");
		}
		temp = new StringBuffer("");
		if(Tools.isEmpty(param.get("beginAccount")) 
				&& !Tools.isEmpty(param.get("endAccount")) && Tools.isEmpty(param.get("accountCode"))) {
			condition.append(" and LOCAL_ACCOUNT <= '"+param.get("endAccount").toString()+"'");
		}
		if(!Tools.isEmpty(param.get("beginAccount")) 
				&& Tools.isEmpty(param.get("endAccount")) && Tools.isEmpty(param.get("accountCode"))) {
			condition.append(" and LOCAL_ACCOUNT >= '"+param.get("beginAccount").toString()+"'");
		}
		temp = new StringBuffer("");
		if(Tools.isEmpty(param.get("beginAccount")) 
				&& Tools.isEmpty(param.get("endAccount")) && !Tools.isEmpty(param.get("accountCode"))) {
			condition.append(" and LOCAL_ACCOUNT in (");
			for(HashMap<String,String> obj : (List<HashMap<String,String>>)param.get("list")){
				temp.append("'"+obj.get("id")+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+")");
		}
		if(!Tools.isEmpty(param.get("account_s_day"))){
			condition.append(" and isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') >= CONVERT(varchar(100),'" + param.get("account_s_day").toString()+"')");
		}
		if(!Tools.isEmpty(param.get("account_e_day"))){
			condition.append(" and isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') <= CONVERT(varchar(100),'" + param.get("account_e_day").toString()+"')");
		}
		temp = new StringBuffer("");
		if(!Tools.isEmpty(param.get("beginNo")) 
				&& !Tools.isEmpty(param.get("endNo")) && !Tools.isEmpty(param.get("noCode"))) {
			condition.append(" and ((ENTRY_NO >= '"+param.get("beginNo").toString()+"' and ENTRY_NO <= '"+param.get("endNo").toString()+"') or ENTRY_NO in (");
			for(String obj : (List<String>)param.get("ids")){
				temp.append("'"+obj+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+"))");
		}
		if(!Tools.isEmpty(param.get("beginNo")) 
				&& !Tools.isEmpty(param.get("endNo")) && Tools.isEmpty(param.get("noCode"))) {
			condition.append(" and ENTRY_NO >= '"+param.get("beginNo").toString()+"' and ENTRY_NO <= '"+param.get("endNo").toString()+"'");
		}
		if(!Tools.isEmpty(param.get("beginNo")) 
				&& Tools.isEmpty(param.get("endNo")) && !Tools.isEmpty(param.get("noCode"))) {
			condition.append(" and (ENTRY_NO >= '"+param.get("beginNo").toString()+"' or ENTRY_NO in (");
			for(String obj : (List<String>)param.get("ids")){
				temp.append("'"+obj+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+"))");
		}
		temp = new StringBuffer("");
		if(Tools.isEmpty(param.get("beginNo")) 
				&& !Tools.isEmpty(param.get("endNo")) && !Tools.isEmpty(param.get("noCode"))) {
			condition.append(" and (ENTRY_NO <= '"+param.get("endNo").toString()+"' or ENTRY_NO in (");
			for(String obj : (List<String>)param.get("ids")){
				temp.append("'"+obj+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+"))");
		}
		temp = new StringBuffer("");
		if(Tools.isEmpty(param.get("beginNo")) 
				&& !Tools.isEmpty(param.get("endNo")) && Tools.isEmpty(param.get("noCode"))) {
			condition.append(" and ENTRY_NO <= '"+param.get("endNo").toString()+"'");
		}
		if(!Tools.isEmpty(param.get("beginNo")) 
				&& Tools.isEmpty(param.get("endNo")) && Tools.isEmpty(param.get("noCode"))) {
			condition.append(" and ENTRY_NO >= '"+param.get("beginNo").toString()+"'");
		}
		temp = new StringBuffer("");
		if(Tools.isEmpty(param.get("beginNo")) 
				&& Tools.isEmpty(param.get("endNo")) && !Tools.isEmpty(param.get("noCode"))) {
			condition.append(" and ENTRY_NO in (");
			for(String obj : (List<String>)param.get("ids")){
				temp.append("'"+obj+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+")");
		}
		if(!Tools.isEmpty(param.get("no_s_day"))){
			condition.append(" and isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') >= CONVERT(varchar(100),'" + param.get("no_s_day").toString()+"')");
		}
		if(!Tools.isEmpty(param.get("no_e_day"))){
			condition.append(" and isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') <= CONVERT(varchar(100),'" + param.get("no_e_day").toString()+"')");
		}
		if(!Tools.isEmpty(param.get("beginAmount"))){
			condition.append(" and AMOUNT_LOCALDB >= " + param.get("beginAmount").toString());
		}
		if(!Tools.isEmpty(param.get("endAmount"))){
			condition.append(" and AMOUNT_LOCALDB <= " + param.get("endAmount").toString());
		}
		if(!Tools.isEmpty(param.get("amount_s_day"))){
			condition.append(" and isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') >= CONVERT(varchar(100),'" + param.get("amount_s_day").toString()+"')");
		}
		if(!Tools.isEmpty(param.get("amount_e_day"))){
			condition.append(" and isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') <= CONVERT(varchar(100),'" + param.get("amount_e_day").toString()+"')");
		}
		if(!Tools.isEmpty(param.get("note_s_day"))){
			condition.append(" and isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') >= CONVERT(varchar(100),'" + param.get("note_s_day").toString()+"')");
		}
		if(!Tools.isEmpty(param.get("note_e_day"))){
			condition.append(" and isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') <= CONVERT(varchar(100),'" + param.get("note_e_day").toString()+"')");
		}
		if(!Tools.isEmpty(param.get("noteTxt"))){
			condition.append(" and SENTRY_DESC1 like '" + param.get("noteTxt").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("noteTxt"))){
			condition.append(" and SENTRY_DESC1 like '" + param.get("noteTxt").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence1"))){
			condition.append(" and REFRENCE1 like '" + param.get("refrence1").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence2"))){
			condition.append(" and REFRENCE2 like '" + param.get("refrence2").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence3"))){
			condition.append(" and REFRENCE3 like '" + param.get("refrence3").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence4"))){
			condition.append(" and REFRENCE4 like '" + param.get("refrence4").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence5"))){
			condition.append(" and REFRENCE5 like '" + param.get("refrence5").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence6"))){
			condition.append(" and REFRENCE6 like '" + param.get("refrence6").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence7"))){
			condition.append(" and REFRENCE7 like '" + param.get("refrence7").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence8"))){
			condition.append(" and REFRENCE8 like '" + param.get("refrence8").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence9"))){
			condition.append(" and REFRENCE9 like '" + param.get("refrence9").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence10"))){
			condition.append(" and REFRENCE10 like '" + param.get("refrence10").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence11"))){
			condition.append(" and REFRENCE11 like '" + param.get("refrence11").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence12"))){
			condition.append(" and REFRENCE12 like '" + param.get("refrence12").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence13"))){
			condition.append(" and REFRENCE13 like '" + param.get("refrence13").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence14"))){
			condition.append(" and REFRENCE14 like '" + param.get("refrence14").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence15"))){
			condition.append(" and REFRENCE15 like '" + param.get("refrence15").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence16"))){
			condition.append(" and REFRENCE16 like '" + param.get("refrence16").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence17"))){
			condition.append(" and REFRENCE17 like '" + param.get("refrence17").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence18"))){
			condition.append(" and REFRENCE18 like '" + param.get("refrence18").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence19"))){
			condition.append(" and REFRENCE19 like '" + param.get("refrence19").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence20"))){
			condition.append(" and REFRENCE20 like '" + param.get("refrence20").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("fiscal_year"))){
			condition.append(" and FISCAL_YEAR = '" + param.get("fiscal_year").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("fiscal_period"))){
			condition.append(" and FISCAL_PERIOD = '" + param.get("fiscal_period").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("makeUser"))){
			condition.append(" and OPERATER like '" + param.get("makeUser").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("is_includ"))){
			if(param.get("is_includ").equals("2"))
				condition.append(" and POST_STATUS = 1");
			if(param.get("is_includ").equals("3"))
				condition.append(" and POST_STATUS = 0");
		}
		temp = new StringBuffer("");
		if(!Tools.isEmpty(param.get("items"))) {
			condition.append(" and LCURRENCY in (");
			for(String obj : (List<String>)param.get("ids1")){
				temp.append("'"+obj+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+")");
		}

		conSql.append(condition+") itemsCount");
		
		long count = jdbcTemplate.queryForLong(conSql.toString());
		
		System.out.println("consql======="+conSql);
		
		sql.append(condition+") as a) s where rn <= "+count+" and rn >= 0");
		ArrayList<HashMap<String,Object>> result = (ArrayList<HashMap<String, Object>>) jdbcTemplate.query(sql.toString(),new RowMapper() {     
	          public Object mapRow(ResultSet rs, int rowNum) throws SQLException {     
	        	  HashMap<String,Object> post = new HashMap<String,Object>();
	        	  post.put("GENERAL_LEDGER_HID", rs.getObject(1));
	        	  post.put("FISCAL_YEAR", rs.getObject(2));
	        	  post.put("FISCAL_PERIOD", rs.getObject(3));
	        	  post.put("LOCK_DESC", rs.getObject(4));
	        	  post.put("LOCAL_ACCOUNT", rs.getObject(5));
	        	  post.put("ENTRY_NO", rs.getObject(6));
	        	  post.put("ENTRY_NO_NEW", rs.getObject(7));
	        	  post.put("AMOUNT_LOCALDB", rs.getObject(8));
	        	  post.put("AMOUNT_LOCALCR", rs.getObject(9));
	        	  post.put("POSTING_DATE", rs.getObject(10));
	        	  post.put("ENTRY_DATE", rs.getObject(11));
	        	  post.put("LCURRENCY", rs.getObject(12));
	        	  post.put("SENTRY_DESC1", rs.getObject(13));
	        	  post.put("LOCAL_ACCOUNT_DESC", rs.getObject(14));
	        	  post.put("CHINA_DESC", rs.getObject(15));
	        	  post.put("ERP_ALL_ACCOUNT", rs.getObject(16));
	        	  post.put("ERP_ALL_ACCOUNT_DESC", rs.getObject(17));
	        	  post.put("REFRENCE1", rs.getObject(18));
	        	  post.put("REFRENCE2", rs.getObject(19));
	        	  post.put("REFRENCE3", rs.getObject(20));
	        	  post.put("REFRENCE4", rs.getObject(21));
	        	  post.put("REFRENCE5", rs.getObject(22));
	        	  post.put("REFRENCE6", rs.getObject(23));
	        	  post.put("REFRENCE7", rs.getObject(24));
	        	  post.put("REFRENCE8", rs.getObject(25));
	        	  post.put("REFRENCE9", rs.getObject(26));
	        	  post.put("REFRENCE10", rs.getObject(27));
	        	  post.put("REFRENCE11", rs.getObject(28));
	        	  post.put("REFRENCE12", rs.getObject(29));
	        	  post.put("REFRENCE13", rs.getObject(30));
	        	  post.put("REFRENCE14", rs.getObject(31));
	        	  post.put("REFRENCE15", rs.getObject(32));
	        	  post.put("REFRENCE16", rs.getObject(33));
	        	  post.put("REFRENCE17", rs.getObject(34));
	        	  post.put("REFRENCE18", rs.getObject(35));
	        	  post.put("REFRENCE19", rs.getObject(36));
	        	  post.put("REFRENCE20", rs.getObject(37));
	        	  post.put("OPERATER", rs.getObject(38));
	        	  post.put("POST_STATUS", rs.getObject(39));
	        	  post.put("IMPORT_TYPE", rs.getObject(40));
	              return post;     
	          }    
	       });		
		return result;
	//	return tcodeMapper.getSearchList(param);
    }
    
    @Override
    public int getSearchListCount(HashMap<String,Object> param){
    	StringBuffer temp = new StringBuffer("");
    	StringBuffer conSql = new StringBuffer("select count(*) as num from (select * from HPFS_D_GENERAL_LEDGER where 1 = 1");
    	StringBuffer condition = new StringBuffer("");
		if(!Tools.isEmpty(param.get("company_id"))){
			condition.append(" and COMPANY_ID = " + param.get("company_id").toString());
		}
		if(!Tools.isEmpty(param.get("beginAccount")) 
				&& !Tools.isEmpty(param.get("endAccount")) && !Tools.isEmpty(param.get("accountCode"))) {
			condition.append(" and ((LOCAL_ACCOUNT >= '"+param.get("beginAccount").toString()+"' and LOCAL_ACCOUNT <= '"+param.get("endAccount").toString()+"') or LOCAL_ACCOUNT in (");
			for(HashMap<String,String> obj : (List<HashMap<String,String>>)param.get("list")){
				temp.append("'"+obj.get("id")+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+"))");
		}
		if(!Tools.isEmpty(param.get("beginAccount")) 
				&& !Tools.isEmpty(param.get("endAccount")) && Tools.isEmpty(param.get("accountCode"))) {
			condition.append(" and LOCAL_ACCOUNT >= '"+param.get("beginAccount").toString()+"' and LOCAL_ACCOUNT <= '"+param.get("endAccount").toString()+"'");
		}
		if(!Tools.isEmpty(param.get("beginAccount")) 
				&& Tools.isEmpty(param.get("endAccount")) && !Tools.isEmpty(param.get("accountCode"))) {
			condition.append(" and (LOCAL_ACCOUNT >= '"+param.get("beginAccount").toString()+"' or LOCAL_ACCOUNT in (");
			for(HashMap<String,String> obj : (List<HashMap<String,String>>)param.get("list")){
				temp.append("'"+obj.get("id")+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+"))");
		}
		temp = new StringBuffer("");
		if(Tools.isEmpty(param.get("beginAccount")) 
				&& !Tools.isEmpty(param.get("endAccount")) && !Tools.isEmpty(param.get("accountCode"))) {
			condition.append(" and (LOCAL_ACCOUNT <= '"+param.get("endAccount").toString()+"' or LOCAL_ACCOUNT in (");
			for(HashMap<String,String> obj : (List<HashMap<String,String>>)param.get("list")){
				temp.append("'"+obj.get("id")+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+"))");
		}
		temp = new StringBuffer("");
		if(Tools.isEmpty(param.get("beginAccount")) 
				&& !Tools.isEmpty(param.get("endAccount")) && Tools.isEmpty(param.get("accountCode"))) {
			condition.append(" and LOCAL_ACCOUNT <= '"+param.get("endAccount").toString()+"'");
		}
		if(!Tools.isEmpty(param.get("beginAccount")) 
				&& Tools.isEmpty(param.get("endAccount")) && Tools.isEmpty(param.get("accountCode"))) {
			condition.append(" and LOCAL_ACCOUNT >= '"+param.get("beginAccount").toString()+"'");
		}
		temp = new StringBuffer("");
		if(Tools.isEmpty(param.get("beginAccount")) 
				&& Tools.isEmpty(param.get("endAccount")) && !Tools.isEmpty(param.get("accountCode"))) {
			condition.append(" and LOCAL_ACCOUNT in (");
			for(HashMap<String,String> obj : (List<HashMap<String,String>>)param.get("list")){
				temp.append("'"+obj.get("id")+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+")");
		}
		if(!Tools.isEmpty(param.get("account_s_day"))){
			condition.append(" and isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') >= CONVERT(varchar(100),'" + param.get("account_s_day").toString()+"')");
		}
		if(!Tools.isEmpty(param.get("account_e_day"))){
			condition.append(" and isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') <= CONVERT(varchar(100),'" + param.get("account_e_day").toString()+"')");
		}
		temp = new StringBuffer("");
		if(!Tools.isEmpty(param.get("beginNo")) 
				&& !Tools.isEmpty(param.get("endNo")) && !Tools.isEmpty(param.get("noCode"))) {
			condition.append(" and ((ENTRY_NO >= '"+param.get("beginNo").toString()+"' and ENTRY_NO <= '"+param.get("endNo").toString()+"') or ENTRY_NO in (");
			for(String obj : (List<String>)param.get("ids")){
				temp.append("'"+obj+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+"))");
		}
		if(!Tools.isEmpty(param.get("beginNo")) 
				&& !Tools.isEmpty(param.get("endNo")) && Tools.isEmpty(param.get("noCode"))) {
			condition.append(" and ENTRY_NO >= '"+param.get("beginNo").toString()+"' and ENTRY_NO <= '"+param.get("endNo").toString()+"'");
		}
		if(!Tools.isEmpty(param.get("beginNo")) 
				&& Tools.isEmpty(param.get("endNo")) && !Tools.isEmpty(param.get("noCode"))) {
			condition.append(" and (ENTRY_NO >= '"+param.get("beginNo").toString()+"' or ENTRY_NO in (");
			for(String obj : (List<String>)param.get("ids")){
				temp.append("'"+obj+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+"))");
		}
		temp = new StringBuffer("");
		if(Tools.isEmpty(param.get("beginNo")) 
				&& !Tools.isEmpty(param.get("endNo")) && !Tools.isEmpty(param.get("noCode"))) {
			condition.append(" and (ENTRY_NO <= '"+param.get("endNo").toString()+"' or ENTRY_NO in (");
			for(String obj : (List<String>)param.get("ids")){
				temp.append("'"+obj+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+"))");
		}
		temp = new StringBuffer("");
		if(Tools.isEmpty(param.get("beginNo")) 
				&& !Tools.isEmpty(param.get("endNo")) && Tools.isEmpty(param.get("noCode"))) {
			condition.append(" and ENTRY_NO <= '"+param.get("endNo").toString()+"'");
		}
		if(!Tools.isEmpty(param.get("beginNo")) 
				&& Tools.isEmpty(param.get("endNo")) && Tools.isEmpty(param.get("noCode"))) {
			condition.append(" and ENTRY_NO >= '"+param.get("beginNo").toString()+"'");
		}
		temp = new StringBuffer("");
		if(Tools.isEmpty(param.get("beginNo")) 
				&& Tools.isEmpty(param.get("endNo")) && !Tools.isEmpty(param.get("noCode"))) {
			condition.append(" and ENTRY_NO in (");
			for(String obj : (List<String>)param.get("ids")){
				temp.append("'"+obj+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+")");
		}
		if(!Tools.isEmpty(param.get("no_s_day"))){
			condition.append(" and isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') >= CONVERT(varchar(100),'" + param.get("no_s_day").toString()+"')");
		}
		if(!Tools.isEmpty(param.get("no_e_day"))){
			condition.append(" and isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') <= CONVERT(varchar(100),'" + param.get("no_e_day").toString()+"')");
		}
		if(!Tools.isEmpty(param.get("beginAmount"))){
			condition.append(" and AMOUNT_LOCALDB >= " + param.get("beginAmount").toString());
		}
		if(!Tools.isEmpty(param.get("endAmount"))){
			condition.append(" and AMOUNT_LOCALDB <= " + param.get("endAmount").toString());
		}
		if(!Tools.isEmpty(param.get("amount_s_day"))){
			condition.append(" and isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') >= CONVERT(varchar(100),'" + param.get("amount_s_day").toString()+"')");
		}
		if(!Tools.isEmpty(param.get("amount_e_day"))){
			condition.append(" and isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') <= CONVERT(varchar(100),'" + param.get("amount_e_day").toString()+"')");
		}
		if(!Tools.isEmpty(param.get("note_s_day"))){
			condition.append(" and isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') >= CONVERT(varchar(100),'" + param.get("note_s_day").toString()+"')");
		}
		if(!Tools.isEmpty(param.get("note_e_day"))){
			condition.append(" and isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') <= CONVERT(varchar(100),'" + param.get("note_e_day").toString()+"')");
		}
		if(!Tools.isEmpty(param.get("noteTxt"))){
			condition.append(" and SENTRY_DESC1 like '" + param.get("noteTxt").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("noteTxt"))){
			condition.append(" and SENTRY_DESC1 like '" + param.get("noteTxt").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence1"))){
			condition.append(" and REFRENCE1 like '" + param.get("refrence1").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence2"))){
			condition.append(" and REFRENCE2 like '" + param.get("refrence2").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence3"))){
			condition.append(" and REFRENCE3 like '" + param.get("refrence3").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence4"))){
			condition.append(" and REFRENCE4 like '" + param.get("refrence4").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence5"))){
			condition.append(" and REFRENCE5 like '" + param.get("refrence5").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence6"))){
			condition.append(" and REFRENCE6 like '" + param.get("refrence6").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence7"))){
			condition.append(" and REFRENCE7 like '" + param.get("refrence7").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence8"))){
			condition.append(" and REFRENCE8 like '" + param.get("refrence8").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence9"))){
			condition.append(" and REFRENCE9 like '" + param.get("refrence9").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence10"))){
			condition.append(" and REFRENCE10 like '" + param.get("refrence10").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence11"))){
			condition.append(" and REFRENCE11 like '" + param.get("refrence11").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence12"))){
			condition.append(" and REFRENCE12 like '" + param.get("refrence12").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence13"))){
			condition.append(" and REFRENCE13 like '" + param.get("refrence13").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence14"))){
			condition.append(" and REFRENCE14 like '" + param.get("refrence14").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence15"))){
			condition.append(" and REFRENCE15 like '" + param.get("refrence15").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence16"))){
			condition.append(" and REFRENCE16 like '" + param.get("refrence16").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence17"))){
			condition.append(" and REFRENCE17 like '" + param.get("refrence17").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence18"))){
			condition.append(" and REFRENCE18 like '" + param.get("refrence18").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence19"))){
			condition.append(" and REFRENCE19 like '" + param.get("refrence19").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("refrence20"))){
			condition.append(" and REFRENCE20 like '" + param.get("refrence20").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("fiscal_year"))){
			condition.append(" and FISCAL_YEAR = '" + param.get("fiscal_year").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("fiscal_period"))){
			condition.append(" and FISCAL_PERIOD = '" + param.get("fiscal_period").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("makeUser"))){
			condition.append(" and OPERATER like '" + param.get("makeUser").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("is_includ"))){
			if(param.get("is_includ").equals("2"))
				condition.append(" and POST_STATUS = 1");
			if(param.get("is_includ").equals("3"))
				condition.append(" and POST_STATUS = 0");
		}
		temp = new StringBuffer("");
		if(!Tools.isEmpty(param.get("items"))) {
			condition.append(" and LCURRENCY in (");
			for(String obj : (List<String>)param.get("ids1")){
				temp.append("'"+obj+"',");
			}
			condition.append(temp.substring(0, temp.length()-1)+")");
		}
		
		conSql.append(condition+") itemsCount");
		
		long count = jdbcTemplate.queryForLong(conSql.toString());

    	return (int)count;
    }
    
    @Override
    public ArrayList<HashMap<String,Object>> getAccountDetailForExport(HashMap<String,Object> param){

    	StringBuffer conSql = new StringBuffer("select count(*) as num from (select * from HPFS_D_GENERAL_LEDGER where 1 = 1");
    	StringBuffer condition = new StringBuffer("");
		StringBuffer sql =new StringBuffer("	select * from ( select top 100 percent *,row_number() over ( order by "+param.get("orderBy")+") as rn " +
				"from ( select fiscal_period,fiscal_year,company_id,isnull(CONVERT(varchar(100),ENTRY_DATE, 23),'') as date_time" +
				", LCURRENCY as lcu, LOCAL_ACCOUNT as local_account, LOCAL_ACCOUNT_DESC as account_desc" +
  			",CHINA_DESC, ERP_ALL_ACCOUNT, ERP_ALL_ACCOUNT_DESC, REFRENCE1, REFRENCE2, REFRENCE3, REFRENCE4, REFRENCE5" +
 			",REFRENCE6, REFRENCE7, REFRENCE8, REFRENCE9, REFRENCE10, REFRENCE11, REFRENCE12, REFRENCE13, REFRENCE14, REFRENCE15, REFRENCE16" +
 			",REFRENCE17, REFRENCE18, REFRENCE19, REFRENCE20,ENTRY_NO,SENTRY_DESC1" +
			",case when '"+param.get("type").toString()+"' = '1' then AMOUNT_LOCALDB" + 
			" when '"+param.get("type").toString()+"' = '3' then AMOUNT_LOCALDB" +
			" else null end as db" +
			",case when '"+param.get("type").toString()+"' = '1' then null " +
			" when '"+param.get("type").toString()+"' = '3' then AMOUNT_LOCALCR" +
			" else AMOUNT_LOCALCR end as cr" +
			",case when '"+param.get("type").toString()+"' = '3' then isnull(AMOUNT_LOCALCR,0) - isnull(AMOUNT_LOCALDB,0)" +
			" else null end as amount1" +
			",case when AMOUNT_LOCALDB != 0 " +
			" then (select max(AMOUNT_LOCALCR) from HPFS_D_GENERAL_LEDGER where ENTRY_NO = s.ENTRY_NO and LOCAL_ACCOUNT != s.LOCAL_ACCOUNT)" +
			" when AMOUNT_LOCALCR != 0" +
		  	" then (select max(AMOUNT_LOCALDB) from HPFS_D_GENERAL_LEDGER where ENTRY_NO = s.ENTRY_NO and LOCAL_ACCOUNT != s.LOCAL_ACCOUNT)" +
			" end as otherAmount" +
			",case when AMOUNT_LOCALDB != 0 then " +
			" (select top 1 LOCAL_ACCOUNT from HPFS_D_GENERAL_LEDGER where ENTRY_NO = s.ENTRY_NO and LOCAL_ACCOUNT != s.LOCAL_ACCOUNT  " +
			" and AMOUNT_LOCALCR = (select max(AMOUNT_LOCALCR) from HPFS_D_GENERAL_LEDGER where ENTRY_NO = s.ENTRY_NO and LOCAL_ACCOUNT != s.LOCAL_ACCOUNT))" +
			" when AMOUNT_LOCALCR != 0 then" +
		 	" (select top 1 LOCAL_ACCOUNT from HPFS_D_GENERAL_LEDGER where ENTRY_NO = s.ENTRY_NO and LOCAL_ACCOUNT != s.LOCAL_ACCOUNT " +
			" and AMOUNT_LOCALDB = (select max(AMOUNT_LOCALDB) from HPFS_D_GENERAL_LEDGER where ENTRY_NO = s.ENTRY_NO and LOCAL_ACCOUNT != s.LOCAL_ACCOUNT))" +
			" end as otherAccount" +
			" from HPFS_D_GENERAL_LEDGER as s) as a where 1 = 1 ");
    	
		if(!Tools.isEmpty(param.get("account"))){
			condition.append(" and local_account = '" + param.get("account").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("fiscal_year"))){
			condition.append(" and fiscal_year = '" + param.get("fiscal_year").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("fiscal_period"))){
			condition.append(" and fiscal_period = '" + param.get("fiscal_period").toString() +"'");
		}
		if(!Tools.isEmpty(param.get("company_id"))){
			condition.append(" and company_id = '" + param.get("company_id").toString() +"'");
		}

		conSql.append(condition+") itemsCount");
    	long count = jdbcTemplate.queryForLong(conSql.toString());
    //	param.put("pageCount", count);
    	sql.append(condition+") s where rn <= "+count+" and rn >= 0");
		@SuppressWarnings("unchecked")
		ArrayList<HashMap<String,Object>> result = (ArrayList<HashMap<String, Object>>) jdbcTemplate.query(sql.toString(),new RowMapper() {     
	          public Object mapRow(ResultSet rs, int rowNum) throws SQLException {     
	        	  HashMap<String,Object> post = new HashMap<String,Object>();
	        	  post.put("fiscal_period", rs.getObject(1));
	        	  post.put("fiscal_year", rs.getObject(2));
	        	  post.put("company_id", rs.getObject(3));
	        	  post.put("date_time", rs.getObject(4));
	        	  post.put("lcu", rs.getObject(5));
	        	  post.put("local_account", rs.getObject(6));
	        	  post.put("account_desc", rs.getObject(7));
	        	  post.put("CHINA_DESC", rs.getObject(8));
	        	  post.put("ERP_ALL_ACCOUNT", rs.getObject(9));
	        	  post.put("ERP_ALL_ACCOUNT_DESC", rs.getObject(10));
	        	  post.put("REFRENCE1", rs.getObject(11));
	        	  post.put("REFRENCE2", rs.getObject(12));
	        	  post.put("REFRENCE3", rs.getObject(13));
	        	  post.put("REFRENCE4", rs.getObject(14));
	        	  post.put("REFRENCE5", rs.getObject(15));
	        	  post.put("REFRENCE6", rs.getObject(16));
	        	  post.put("REFRENCE7", rs.getObject(17));
	        	  post.put("REFRENCE8", rs.getObject(18));
	        	  post.put("REFRENCE9", rs.getObject(19));
	        	  post.put("REFRENCE10", rs.getObject(20));
	        	  post.put("REFRENCE11", rs.getObject(21));
	        	  post.put("REFRENCE12", rs.getObject(22));
	        	  post.put("REFRENCE13", rs.getObject(23));
	        	  post.put("REFRENCE14", rs.getObject(24));
	        	  post.put("REFRENCE15", rs.getObject(25));
	        	  post.put("REFRENCE16", rs.getObject(26));
	        	  post.put("REFRENCE17", rs.getObject(27));
	        	  post.put("REFRENCE18", rs.getObject(28));
	        	  post.put("REFRENCE19", rs.getObject(29));
	        	  post.put("REFRENCE20", rs.getObject(30));
	        	  post.put("ENTRY_NO", rs.getObject(31));
	        	  post.put("SENTRY_DESC1", rs.getObject(32));
	        	  post.put("db", rs.getObject(33));
	        	  post.put("cr", rs.getObject(34));
	        	  post.put("amount1", rs.getObject(35));
	        	  post.put("otherAmount", rs.getObject(36));
	        	  post.put("otherAccount", rs.getObject(37));
	              return post;     
	          }    
	       });	
		double sum_amount = 0;
		if(param.get("type").toString().equals("3")){
			for(int i = 0;i<result.size();i++){
				HashMap<String,Object> obj = result.get(i);
				if(i == 0){
					sum_amount = Double.parseDouble(obj.get("amount1").toString());
				} else {
					sum_amount += Double.parseDouble(obj.get("amount1").toString());
				}

				DecimalFormat df =  new DecimalFormat("#.##");
				String priceStr=df.format(sum_amount);
				obj.put("amount", priceStr);
			//	result.add(obj);
	    	}
		}
		return result;
		//return tcodeMapper.getAccountDetailList(param);
    }
    
    @Override
    public ArrayList<HashMap<String,Object>> getSumAccountList(HashMap<String,Object> param){
    	ArrayList<HashMap<String,Object>> result = new ArrayList<HashMap<String,Object>>();
    	ArrayList<HashMap<String,Object>> list = tcodeMapper.getSumAccountList(param);
    	double sum_amount = 0;
    	for(HashMap<String,Object> obj : list){
			if(obj.get("num").equals("0")){
				sum_amount = Double.parseDouble(obj.get("openning").toString());
				obj = new HashMap<String,Object>();
				obj.put("amount", sum_amount);
			}else{
				if(obj.get("db")!=null && obj.get("cr") != null){
					if(obj.get("num").equals("1")){
	    				sum_amount = Double.parseDouble(obj.get("amount").toString())+ Double.parseDouble(obj.get("openning").toString());
	    			} else {
	    				sum_amount += Double.parseDouble(obj.get("amount").toString());
	    			}
				} else{
	    			break;
	    		}
			}
			DecimalFormat df =  new DecimalFormat("#.##");
			String priceStr=df.format(sum_amount);
			obj.put("sum_amount", priceStr);
			result.add(obj);
    	}
    	param.put("pageCount", result.size());
    	return result;
    }
    
    @Override
    public HashMap<String,Object> checkTcode(HashMap<String,Object> param){
    	return tcodeMapper.checkTcode(param);
    }
    
    @Override
	public int updateChinaDesc(HashMap<String,Object> param){
		try{
			tcodeMapper.updateChinaDesc(param);
			if(!param.get("desc_range").toString().equals("1")){
				tcodeMapper.updateChinaDescForOther(param);
			}
			return 0;
		}catch(Exception ex){
			logger.error(ex.getMessage());
			return -1;
		}
	}
    
    @Override
    public int checkTcodeFromRefrence(HashMap<String,Object> param){
    	String dimesion = tcodeMapper.getDimesionFromTcode(param);
    	param.put("dimesion_num",dimesion);
    	param.put("dimesion", "a.refrence"+dimesion);
    	if(!dimesion.equals("0")){  	
    	    return  tcodeMapper.checkTcodeFromRefrence(param);
    	}else {
    		return 1;
    	} 
   }
    
    @Override
    public String getErrorInfo(HashMap<String,Object> param){
    	return tcodeMapper.getErrorInfo(param);
    }
    
    @Override
	public ArrayList<HashMap<String, Object>> reportErrorDetail(HashMap<String, Object> param) {
		// TODO Auto-generated method stub
		return tcodeMapper.reportErrorDetail(param);
	}
}
